<?php

namespace App\Model\Face;

use App\Lib\Utils;
use App\Model\Base;
use App\Model\BaseModel;
use App\Model\Device\DeviceModel;

use App\Model\Device\Group as GroupModel;
use EasySwoole\Http\Message\Status;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class CompareModel extends BaseModel
{

	public $tableName = "sc_compare";
	public $params;

	public function getCompareData($joinTable, $field = '*', $where = [], $page = 1, $pageSize = 10, $order = ['A.id', 'desc'])
    {
		$this->withTotalCount()
		    ->field($field)
		    ->order($order[0], $order[1])
		    ->alias('A')
			->join($joinTable.' as B','A.id = B.compare_id','LEFT')
			->join('sc_device as C', 'A.device_id = C.device_id', 'LEFT')
			->join('sc_device_group as D', 'C.group_id = D.id', 'LEFT')
            ->join('sc_face as E', 'A.face_id = E.id', 'LEFT')
			->where($where)
            ->page($page, $pageSize)
            ->all();

		$count = $this->lastQueryResult()->getTotalCount();
        $lists = $this->lastQueryResult()->getResult();

        return [
            'count' => $count,
            'lists' => $lists
        ];
	}

    public function getAllCompare($joinTable, $field = '*', $where = [], $order = ['A.id', 'desc'])
    {
        $this->withTotalCount()
            ->field($field)
            ->order($order[0], $order[1])
            ->alias('A')
            ->join($joinTable.' as B','A.id = B.compare_id','LEFT')
            ->join('sc_device as C', 'A.device_id = C.device_id', 'LEFT')
            ->join('sc_device_group as D', 'C.group_id = D.id', 'LEFT')
            ->join('sc_face as E', 'A.face_id = E.id', 'LEFT')
            ->where($where)
            ->all();

        $lists = $this->lastQueryResult()->getResult();
        return $lists;
    }

    public function getDevCompare($field = '*', $where = [], $order = ['A.id', 'desc']){
        $this->withTotalCount()
            ->field($field)
            ->order($order[0], $order[1])
            ->alias('A')
            ->join('mymk_device as B', 'A.deviceremark=B.deviceremark', 'LEFT')
            ->where($where)
            ->all();

        $lists = $this->lastQueryResult()->getResult();

        // $list = $lastResult->getResult();
        return $lists;
    }

    public function setCondition()
    {
        $condition = [];

        // 设备类型
        if (isset($this->params['device_type']) && !empty($this->params['device_type'])) {
            $condition['C.device_type'] = $this->params['device_type'];
        }

        // 姓名
        if (isset($this->params['name']) && !empty($this->params['name'])) {
            $condition['A.name'] = ['%'.$this->params['name'].'%','like'];
        }

        // 人脸库
        if (isset($this->params['face_group_id']) && !empty($this->params['face_group_id'])) {
            $fids = explode(',', $this->params['face_group_id']);
            $condition['E.face_group_id'] = [$fids, 'IN'];
        }

        // 任务类型
        if (isset($this->params['mode']) && ($this->params['mode'] !== '')) {
            $mode = intval($this->params['mode']);
            $condition['A.mode'] = $mode;
        }

        // 时间范围
        if (isset($this->params['capture_time']) && !empty($this->params['capture_time'])) {
            $date = $this->params['capture_time'];
            $firsttime = strtotime($date[0]);
            $lasttime = strtotime($date[1]);
            $condition['A.capture_time'] = [[$firsttime, $lasttime], 'between'];
        }

        // 时间段
        if (isset($this->params['daterange']) && !empty($this->params['daterange'])) {
            $range = Utils::setDateRange($this->params['daterange']);
            if ($range) {
                $condition['A.capture_time'] = [$range, 'between'];
            }
        }

        $sttr = [
            'attr_gender', // 性别
            'attr_age', // 年龄
            'attr_beard', // 胡子
            'attr_race',  // 肤色
            'attr_glasses', // 眼镜
        ];

        foreach ($attr as $v) {
            if (isset($this->params[$v]) && !empty($this->params[$v])) {
                $condition['B.'.$v] = $this->params[$v];
            }
        }

        return $condition;
    }

    /**
     * 获取列表数据
     * @return array|bool
     */
    public function getList()
    {
        global $_W;

        $condition = $this->setCondition();

        try {
            $year = date('Y', time());

            $tableName = "sc_compare_{$_W['companyid']}_{$year}";
            $tableName2 = "sc_compare_info_{$_W['companyid']}_{$year}";

            $this->tableName($tableName);

            $columns = 'A.name, A.face_id, 
                    E.person_code, E.face_group_id, 
                    C.device_mac, A.mode, 
                    A.imgpath, A.temper, A.channel, 
                    A.capture_time, A.create_time, 
                    A.compare_result, C.name as device_name, 
                    D.name as group_name';

            $data = $this->getCompareData($tableName2, $columns, $condition, $this->params['page'], $this->params['limit']);

        } catch(\Exception $e) {
            $this->message = $e->getMessage();
            return false;
        }

        //$baseINI = \Yaconf::get("basebt");
        $domain = "http://dev.smart_campus_server.com/webroot";

        if (!empty($data['lists'])) {
            $modeList = ['0' => '陌生人', '1' => '黑名单', '2' => '员工', '3' => 'VIP'];
            foreach ($data['lists'] as &$list) {
                $list['create_time'] = date("Y-m-d H:i:s", $list['create_time']);
                $list['capture_time'] = date("Y-m-d H:i:s", $list['capture_time']);
                $list['compare_result'] = ($list['compare_result']*100).'%';
                $list['imgpath'] = $domain.$list['imgpath'];
                $list['wbmode'] = $modeList[$list['person_type']] ?? '';
                $list['channel'] += 1;
            }
        }
        return $data;
    }

    public function exportExcel($data, $filename, $titles)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->mergeCells('A1:H1');
        $sheet->setCellValue('A1', '通行记录列表');

        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => true,
                'size' => 22
            ]
        ];

        $sheet->getStyle('A1')->applyFromArray($styleArray);

        $styleCenter = [
            'alignment' => [
                'horizontal' => 'center',
                'vertical' => 'center',
            ]
        ];

        $i = 2;
        $j = 0;
        $cell = [
            'A','B','C','D','E','F','G','H','I','J','K','L', 'M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
            'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO', 'AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ',
            'BA','BB','BC','BD', 'BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS', 'BT','BU','BV','BW','BX','BY','BZ',
            'CA','CB','CC','CD','CE','CF','CG','CH', 'CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW', 'CX','CY','CZ'
        ];

        foreach ($titles as $tkey => $tvalue) {
            $sheet->mergeCells($cell[$j].$i.':'.$cell[$j].($i+1));
            $sheet->setCellValue($cell[$j].$i, $tvalue);
            $sheet->getColumnDimension($cell[$j])->setWidth(15);
            $sheet->getStyle($cell[$j].$i)->applyFromArray($styleCenter);
            $j++;
        }

        $i += 2;
        foreach ($data as $line) {
            $s = 0;
            foreach ($line as $key => $item) {
                if ($key != 'imgpath') {
                    $item = Utils::characet($item);
                    $sheet->setCellValue($cell[$s].$i, $item);
                    $sheet->getStyle($cell[$s].$i)->applyFromArray($styleCenter);
                    $s++;
                } else {
                    $filepath = EASYSWOOLE_ROOT.'/webroot/'.$item;
                    if (file_exists($filepath)) {
                        $drawing[$i] = new Drawing();
                        $drawing[$i]->setName('客户照片');
                        $drawing[$i]->setDescription('客户照片');
                        $drawing[$i]->setPath($filepath);
                        $drawing[$i]->setWidth(80);
                        $drawing[$i]->setHeight(80);
                        $drawing[$i]->setCoordinates($cell[$s].$i);
                        $drawing[$i]->setOffsetX(10);
                        $drawing[$i]->setOffsetY(10);
                        $drawing[$i]->setWorksheet($sheet);
                        $sheet->getRowDimension($i)->setRowHeight(80);
                    } else {
                        $item = Utils::characet('-');
                        $sheet->setCellValue($cell[$s].$i, $item);
                        $sheet->getStyle($cell[$s].$i)->applyFromArray($styleCenter);
                    }
                    $s++;
                }
            }
            $i++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save(EASYSWOOLE_ROOT.'/webroot/excel/'.$filename);
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }

    /**
     * 获取抓拍列表
     */
    public function getQuickshotList()
    {
        global $_W;

        $condition = $this->setCondition();

        try {
            $year = date('Y', time());

            $tableName = "sc_compare_{$_W['companyid']}_{$year}";

            $this->tableName($tableName);

            $columns = 'A.id, A.capture_time, A.imgpath, C.name';

            $this->withTotalCount()
                ->field($columns)
                ->order('id', 'desc')
                ->alias('A')
                ->join('sc_device as C', 'A.device_id = C.device_id', 'LEFT')
                ->where($condition)
                ->page($this->params['page'], $this->params['limit'])
                ->all();

            $count = $this->lastQueryResult()->getTotalCount();
            $lists = $this->lastQueryResult()->getResult();

            $data = [
                'count' => $count,
                'lists' => $lists
            ];

        } catch(\Exception $e) {
            $this->message = $e->getMessage();
            return false;
        }

        //$baseINI = \Yaconf::get("basebt");
        $domain = "http://dev.smart_campus_server.com/webroot";

        if (!empty($data['lists'])) {
            foreach ($data['lists'] as &$list) {
                $list['capture_time'] = date("Y-m-d H:i:s", $list['capture_time']);
                $list['imgpath'] = $domain.$list['imgpath'];
            }
        }

        return $data;
    }

    /**
     * 查看抓拍记录
     */
    public function getOne($id)
    {
        global $_W;
        $year = date('Y', time());
        $tableName = "sc_compare_{$_W['companyid']}_{$year}";
        $this->tableName($tableName);

        $m = $this->field('A.id, A.capture_time, A.imgpath, C.name')
            ->alias('A')
            ->join('sc_device as C', 'A.device_id = C.device_id', 'LEFT')
            ->where('A.id', $id)
            ->get();

        if ($m) {
            return $m->toArray();
        } else {
            return null;
        }
    }

    /**
     * 获取抓拍列表
     */
    public function getUpOrDown($id)
    {
        global $_W;

        $condition = $this->setCondition();
        $action = isset($this->params['action']) ? (int)$this->params['action'] : 1;

        try {
            $year = date('Y', time());
            $tableName = "sc_compare_{$_W['companyid']}_{$year}";
            $this->tableName($tableName);
            $columns = 'A.id, A.capture_time, A.imgpath, C.name';

            if ($action > 0) { // 下一条
                $condition['A.id'] = [$id, '<'];
                $data = $this->field($columns)
                    ->order('id', 'desc')
                    ->alias('A')
                    ->join('sc_device as C', 'A.device_id = C.device_id', 'LEFT')
                    ->where($condition)
                    ->limit(1)
                    ->get()
                    ->toArray();

            } else { // 上一条
                $condition['A.id'] = [$id, '>'];
                $data = $this->field($columns)
                    ->order('id', 'asc')
                    ->alias('A')
                    ->join('sc_device as C', 'A.device_id = C.device_id', 'LEFT')
                    ->where($condition)
                    ->limit(1)
                    ->get()
                    ->toArray();
            }
        } catch(\Exception $e) {
            $this->message = $e->getMessage();
            return false;
        }

        //$baseINI = \Yaconf::get("basebt");
        $domain = "http://dev.smart_campus_server.com/webroot";

        if (!empty($data)) {
            $data['capture_time'] = date("Y-m-d H:i:s", $data['capture_time']);
            $data['imgpath'] = $domain.$data['imgpath'];
        } else {
            $this->message = '没有数据了';
            return false;
        }
        return $data;
    }


}